﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Web;
using Ehai.Web.Framework;


namespace Ehai.Web.Framework.Rules
{
    class SqlToExcelRuleHandle:IMsgRuleHandle
    {
        #region IMsgRuleHandle 成员

#if DEBUG
        private int _execStatus = 0;

        public int ExecStatus
        {
            get { return _execStatus; }
        }
#endif
        public const string Name = "sqltoexcel";  

        public string[][] Execute(string[] p, ContextInfo ci)
        {
            string[][] result = { new string[1] { String.Empty } };
            //IDataReader reader = null;
            if (p.Length < 5)
            {
#if DEBUG
                // 操作成功,状态值
                _execStatus = -2;
#endif
                return null;
            }
            for (int n = 0; n < 5; n++)
            {
                if (String.IsNullOrEmpty(p[n]))
                {
#if DEBUG
                    // 操作成功,状态值
                    _execStatus = n;
#endif
                    return null;
                }
            }
            string p5 = (p.Length < 6) ? String.Empty : p[5].Trim();
            string p6 = (p.Length < 7) ? String.Empty : p[6].Trim();
            string p7 = (p.Length < 8) ? String.Empty : p[7].Trim();
            try
            {
                string sql = "select * from " + p[3];
                if (p5.Trim() != String.Empty && p6.Trim() != String.Empty)
                    sql += " where " + p5 + "='" + p6 + "'";
                ApplicationDB acsSql = new ApplicationDB(sql, ci, p7);
                IDataReader reader = acsSql.ExecuteReader();
                DateTime dt = DateTime.Now;//dt.ToString("yyyyMMddHHmmss")
                System.Random r = new Random();
                int urlrandom = r.Next(0, 1000);
                string eurl = dt.ToString("yyyyMMddHHmmss") + DateTime.Now.Millisecond.ToString() + urlrandom + ".xls";
                string OLEDBConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ci.ProjectPath + "excelbak\\" + eurl + ";";
                OLEDBConnStr += " Extended Properties=Excel 8.0;";
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        string sheetName = "[";
                        string[] name = p[4].Split(',');
                        for (int i = 0; i < name.Length; i++)
                        {
                            sheetName += reader[name[i]].ToString() + "_";
                        }
                        sheetName = sheetName.Remove(sheetName.Length - 1);
                        sheetName += "]";
                        //string sheetName = reader[p[4]].ToString();
                        string strCreateTableSQL = @" CREATE TABLE ";
                        strCreateTableSQL += sheetName;
                        strCreateTableSQL += @" ( ";
                        //显示数据库中字段名
                        //for (int j = 0; j < Convert.ToInt32(p[2]) - 1; j++)
                        //    strCreateTableSQL += @" " + reader.GetName(j + Convert.ToInt32(p[1])) + " VARCHAR, ";
                        //strCreateTableSQL += @" " + reader.GetName(Convert.ToInt32(p[1])+Convert.ToInt32(p[2])-1) + " VARCHAR ";
                        for (int j = 0; j < Convert.ToInt32(p[2]) - 1; j++)
                            strCreateTableSQL += @" " + (j + 1).ToString() + " VARCHAR, ";
                        strCreateTableSQL += @" " + (Convert.ToInt32(p[2])) + " VARCHAR ";
                        strCreateTableSQL += @" ) ";
                        OleDbConnection oConn = new OleDbConnection(OLEDBConnStr);
                        OleDbCommand oCreateComm = new OleDbCommand(strCreateTableSQL, oConn);
                        oConn.Open();
                        oCreateComm.ExecuteNonQuery();
                        oConn.Close();

                        for (int i = Convert.ToInt32(p[1]); i <= (reader.FieldCount - Convert.ToInt32(p[2])); i += Convert.ToInt32(p[2]))
                        {
                            string values = String.Empty;
                            for (int k = 0; k < Convert.ToInt32(p[2]); k++)
                                values += "'" + (reader[i + k].ToString() == "null" ? "" : (reader[i + k].ToString() == "()" ? "" : reader[i + k].ToString())) + "',";
                            values = values.Remove(values.Length - 1);
                            OleDbConnection conn = new OleDbConnection(OLEDBConnStr);
                            conn.Open();
                            string strExcel = "insert into " + sheetName + " values(" + values + ")";
                            OleDbCommand OleDbcmd = new OleDbCommand(strExcel, conn);
                            OleDbcmd.ExecuteNonQuery();
                            conn.Close();
                        }
                    }
                }
                if (reader != null) reader.Close();
#if DEBUG
                // 操作成功,状态值
                _execStatus = 0;
#endif
                result[0][0] = "success:" + HttpContext.Current.Server.UrlEncode(eurl);
                return result;
            }
            catch (Exception e)
            {
#if DEBUG
                // 操作成功,状态值
                _execStatus = -1;
#endif
                throw new Ehai.Web.Framework.WebException("Ehai.Web.SqlToExcel", "数据库导入excel异常！", e);
            }
        }

        public string GetHelpInfo()
        {
            return "从数据库将数据导入Excel";
        }

        public string GetHelpDetail()
        {
            return "传入操作类型、表名、列名、列值、关键字、关键字的值和数据库连接字符串</br>" + "例如：{SqlToExcel, 2, 4, users, [us_firstname,us_lastname], us_firstname, deng, id1 }</br>" + "返回：操作的最终结果，若成功，返回success和excel文档的路径；若失败，返回error；如果是抛出了异常，异常的具体信息也返回来"; 
        }

        public string[] GetParametersInfo()
        {
            string[] info = new string[8];
            info[0] = "(必选)处理类型SqlToExcel(不能为空或空字符串)";
            info[1] = "(必选)前多少列不记录循环，整型";
            info[2] = "(必选)每多少列插入作为一行插入，整型";
            info[3] = "(必选)数据库表名";
            info[4] = "(必选)作为sheet名的列,可多列,用逗号隔开，格式为：\"col1,col2,col3......\"";
            info[5] = "(可选)关键列，格式为：\"key1,key2,key3......\"";
            info[6] = "(可选)如果5不为空，6也不能为空)列值，格式为：\"keyvalue1,keyvalue2,keyvalue3......\"";
            info[7] = "(可选)数据库连接字符串的id(可以为空或空字符串)";
            return info;
        }

        #endregion
    }
}
